<?php

namespace App\Console\Commands;

use App\Model\PearlOutput;
use Illuminate\Console\Command;
use Illuminate\Support\Facades\DB;
use Illuminate\Support\Facades\Log;


class GeneratePearlOutputCommand extends Command
{

    private $prev = [];
    private $model = null;
    /**
     * The name and signature of the console command.
     *
     * @var string
     */
    protected $signature = 'generate:pearl_output {type=0}';

    /**
     * The console command description.
     *
     * @var string
     */
    protected $description = 'Command description';

    /**
     * Create a new command instance.
     *
     * @return void
     */
    public function __construct(PearlOutput $pearlOutput)
    {
        parent::__construct();
        $this->model = $pearlOutput;
    }

    /**
     * Execute the console command.
     *
     * @return mixed
     */
    public function handle()
    {
        $type = (int)$this->argument('type');
        if ($type === 0) {
            $this->generateYesterdayData();
        } elseif ($type === 1) {
            $this->generatePearlOutputAll();
        }
    }


    public function generateYesterdayData(){

        $sql = "SELECT
	DATE_SUB(CURDATE(), INTERVAL 1 DAY) created_date,
	COALESCE(SUM(

		IF (
			pearl_type = 3,
			pearl_value,
			0
		)
	),0) recharge_output,
	COALESCE(SUM(

		IF (
			pearl_type = 4,
			pearl_value,
			0
		)
	),0) give_output,
	COALESCE(SUM(

		IF (
			pearl_type = 5,
			pearl_value,
			0
		)
	),0) exchange_output,
	COALESCE(SUM(

		IF (
			pearl_type = 7,
			pearl_value,
			0
		)
	),0) activity_output,
	COALESCE(SUM(
		CASE
		WHEN pearl_type IN (3, 4, 5, 7) THEN
			pearl_value
		ELSE
			0
		END
	),0) today_output,
	COALESCE(SUM(
		CASE
		WHEN pearl_type IN (0, 2) THEN
			pearl_value
		ELSE
			0
		END
	),0) today_consume
FROM
	zb_finance_pearl_stream
WHERE
	change_timestamp BETWEEN UNIX_TIMESTAMP(
		DATE_SUB(CURDATE(), INTERVAL 1 DAY)
	)
AND UNIX_TIMESTAMP(CURDATE())";
        $yesterday_data = DB::connection('api')->selectOne($sql);
        //获取昨日累计值
        $before_yesterday_grand_data = DB::connection('mysql')->selectOne("SELECT today_output,grand_total,now_pearl FROM cms_pearl_output ORDER BY id DESC LIMIT 1");

        // 累计产出 = 昨日累计产出 + 今日产出
        $grand_total = $before_yesterday_grand_data->grand_total + $yesterday_data->today_output;
        // 产出增长率
        if ($before_yesterday_grand_data->today_output > 0) {
            $growth_rate = ($yesterday_data->today_output / $before_yesterday_grand_data->today_output) - 1;
            $growth_rate = sprintf("%.8f", $growth_rate);
        } else {
            $growth_rate = 0;
        }
        $insert_data = collect($yesterday_data)->merge([
            'grand_total' => $grand_total,
            // 当前余量 = 昨日当前余量 + 昨日产出 - 昨日消耗
            'now_pearl' => $before_yesterday_grand_data->now_pearl + $yesterday_data->today_output + $yesterday_data->today_consume,
            'growth_rate' => $growth_rate,
        ])->toArray();
        try {
            if ($this->model->query()->firstOrCreate($insert_data)) {
                $this->info('ok');
            } else {
                $this->info('fail');
            }
        } catch (\Exception $e) {
            Log::debug(__METHOD__);
            Log::debug($e->getMessage());
        }
    }

    public function generatePearlOutputAll(){

        // 最小日期
        $min_date_res = DB::connection('api')->selectOne("SELECT DATE_SUB(MIN(FROM_UNIXTIME(change_timestamp,'%Y-%m-%d')), INTERVAL 1 DAY) AS created_at FROM zb_finance_pearl_stream WHERE change_timestamp>0");
        if (!$min_date_res) return [];
        $sql = "SELECT
	t.created_at created_date,
	COALESCE (pearl.recharge_output, 0) recharge_output,
	COALESCE (pearl.exchange_output, 0) exchange_output,
	COALESCE (pearl.give_output, 0) give_output,
	COALESCE (pearl.activity_output, 0) activity_output,
	COALESCE (pearl.today_output, 0) today_output,
	COALESCE (pearl.today_consume, 0) today_consume
FROM
	(
		SELECT
			@num :=@num + 1,
			DATE_FORMAT(
				adddate(
					'{$min_date_res->created_at}',
					INTERVAL @num DAY
				),
				'%Y-%m-%d'
			) AS created_at
		FROM
			zb_finance_pearl_stream,
			(SELECT @num := 0) t
		WHERE
			ADDDATE(
				'{$min_date_res->created_at}',
				INTERVAL @num DAY
			) < DATE_SUB(CURDATE(), INTERVAL 2 DAY)
		ORDER BY
			created_at ASC
	) t
LEFT JOIN (
	SELECT
		FROM_UNIXTIME(
			change_timestamp,
			'%Y-%m-%d'
		) created_at,
		SUM(

			IF (
				pearl_type = 3,
				pearl_value,
				0
			)
		) recharge_output,
		SUM(

			IF (
				pearl_type = 4,
				pearl_value,
				0
			)
		) give_output,
		SUM(

			IF (
				pearl_type = 5,
				pearl_value,
				0
			)
		) exchange_output,
			SUM(

			IF (
				pearl_type = 7,
				pearl_value,
				0
			)
		) activity_output,
		SUM(
			CASE
			WHEN pearl_type IN (3, 4, 5, 7) THEN
				pearl_value
			ELSE
				0
			END
		) AS today_output,
		SUM(
			CASE
			WHEN pearl_type IN (0, 2) THEN
				pearl_value
			ELSE
				0
			END
		) AS today_consume
	FROM
		zb_finance_pearl_stream
	GROUP BY
		created_at
) pearl ON t.created_at = pearl.created_at";
        $res = DB::connection('api')->cursor($sql);
        $insert_data = [];
        $before_today_total = 0; // 累计产出
        $now_pearl = 0;          // 当前余量
        $grand_today_consume = 0;         // 累计消耗


        foreach ($res as $item) {
            // 累计产出 = 昨日累计产出 + 今日产出
            $before_today_total += $item->today_output;

            // 当前余量 = 累计产出 - 累计消耗
            $grand_today_consume += $item->today_consume;
            $now_pearl = ($before_today_total + $grand_today_consume);

            // 计算产出增长率
            if (empty($this->prev)) {
                $growth_rate = 0;
            } else {
                $prev = array_shift($this->prev);
                if ($prev['today_output'] > 0) {
                    $growth_rate = ($item->today_output / $prev['today_output'] - 1);
                } else {
                    $growth_rate = 0;
                }
            }

            array_push($insert_data, collect($item)->only(['created_date', 'recharge_output', 'exchange_output', 'give_output', 'today_output', 'today_consume', 'activity_output'])->merge([
                'grand_total' => $before_today_total,
                'now_pearl' => $now_pearl,
                'growth_rate' => $growth_rate
            ])->toArray());

            array_unshift($this->prev, ['today_output' => $item->today_output]);
        }
        if ($this->model->batchInsert('pearl_output', $insert_data)) {
            $this->info('ok');
        } else {
            $this->info('fails');
        }
    }


}
